package primetest.impl.dao;

import java.io.IOException;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.inject.Inject;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import primetest.dto.RatingTypeDto;
import primetest.impl.dao.RatingTypeDao;
import primetest.impl.dao.Transaction;

//@Stateless
public class RatingTypeDao implements Serializable {
	/**
	 *
	 */
	private static final long serialVersionUID = 9219184614474616180L;

	private final Logger logger = LoggerFactory.getLogger(this.getClass());


	@Inject
	public RatingTypeDao(){
	}


	public List<RatingTypeDto> getRatingTypeList(Transaction transaction) throws IOException {
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;

		try {

			Connection connection = transaction.getResource(Connection.class);
			prepareStatement = connection.prepareStatement(GET_ALL);
			resultSet = prepareStatement.executeQuery();

			List<RatingTypeDto> items = new ArrayList<RatingTypeDto>();
			while (resultSet.next()) {
				RatingTypeDto ratingTypeDto = makeRatingTypeDto(transaction, resultSet);
				items.add(ratingTypeDto);

			}
			return items;
		} catch (SQLException e) {
			throw new IOException(e);
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					logger.warn(e.getMessage(), e);
				}
			}

			if (prepareStatement != null) {
				try {
					prepareStatement.close();
				} catch (SQLException e) {
					logger.warn(e.getMessage(), e);
				}
			}
		}
	}

	private RatingTypeDto makeRatingTypeDto(Transaction transaction, ResultSet resultSet) throws SQLException, IOException {
		RatingTypeDto ratingTypeDto = new RatingTypeDto(resultSet.getInt(1), resultSet.getString(2),
				resultSet.getString(3), resultSet.getInt(4), resultSet.getBoolean(5));
		return ratingTypeDto;
	}



	public RatingTypeDto getRatingType(Transaction transaction, int seq) throws IOException {
		RatingTypeDto deparmentDto = null;

		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;

		try {
			Connection connection = transaction.getResource(Connection.class);
			prepareStatement = connection.prepareStatement(GET_BY_SEQ);
			prepareStatement.setInt(1, seq);
			resultSet = prepareStatement.executeQuery();

			if(resultSet.next()) {
				deparmentDto = makeRatingTypeDto(transaction, resultSet);

			}
			return deparmentDto;
		} catch (SQLException e) {
			throw new IOException(e);
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					logger.warn(e.getMessage(), e);
				}
			}

			if (prepareStatement != null) {
				try {
					prepareStatement.close();
				} catch (SQLException e) {
					logger.warn(e.getMessage(), e);
				}
			}
		}
	}


	public void addRatingType(Transaction transaction, RatingTypeDto ratingTypeDto) throws IOException {
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;

		try {
			Connection connection = transaction.getResource(Connection.class);
			prepareStatement = connection.prepareStatement(INSERT_ONE);
			prepareStatement.setString(1, ratingTypeDto.getName());
			prepareStatement.setString(2, ratingTypeDto.getTags());
			prepareStatement.setInt(3, ratingTypeDto.getMaxScore());
			prepareStatement.setBoolean(4, ratingTypeDto.getDeleteFlag());
			prepareStatement.executeUpdate();
		} catch (SQLException e) {
			throw new IOException(e);
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					logger.warn(e.getMessage(), e);
				}
			}

			if (prepareStatement != null) {
				try {
					prepareStatement.close();
				} catch (SQLException e) {
					logger.warn(e.getMessage(), e);
				}
			}
		}
	}


	public void updateRatingType(Transaction transaction, RatingTypeDto ratingTypeDto) throws IOException {
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;

		try {
			Connection connection = transaction.getResource(Connection.class);
			prepareStatement = connection.prepareStatement(UPDATE_ONE);
			prepareStatement.setInt(5, ratingTypeDto.getSeq());
			prepareStatement.setString(1, ratingTypeDto.getName());
			prepareStatement.setString(2, ratingTypeDto.getTags());
			prepareStatement.setInt(3, ratingTypeDto.getMaxScore());
			prepareStatement.setBoolean(4, ratingTypeDto.getDeleteFlag());

			prepareStatement.executeUpdate();
		} catch (SQLException e) {
			throw new IOException(e);
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					logger.warn(e.getMessage(), e);
				}
			}

			if (prepareStatement != null) {
				try {
					prepareStatement.close();
				} catch (SQLException e) {
					logger.warn(e.getMessage(), e);
				}
			}
		}

	}


	public void deleteRatingType(Transaction transaction, int ratingTypeSeq) throws IOException {
		PreparedStatement prepareStatement = null;
		ResultSet resultSet = null;

		try {
			Connection connection = transaction.getResource(Connection.class);
			prepareStatement = connection.prepareStatement(DELETE_ONE);
			prepareStatement.setInt(1, ratingTypeSeq);
			prepareStatement.executeUpdate();

		} catch (SQLException e) {
			throw new IOException(e);
		} finally {
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					logger.warn(e.getMessage(), e);
				}
			}

			if (prepareStatement != null) {
				try {
					prepareStatement.close();
				} catch (SQLException e) {
					logger.warn(e.getMessage(), e);
				}
			}
		}

	}

	private static final String GET_ALL = "select seq, name, RATING_LEVEL_BY_TAGS, RATING_MAXSIMUN_SCORE, DELETE_FLAG from s_ratingType order by seq where DELETE_FLAG = 0";
	private static final String GET_BY_SEQ = "select seq, name, RATING_LEVEL_BY_TAGS, RATING_MAXSIMUN_SCORE, DELETE_FLAG from s_ratingType where seq = ?";
//	private static final String INSERT_ONE = "insert into s_ratingType(seq, name, RATING_LEVEL_BY_TAGS, RATING_MAXSIMUN_SCORE, DELETE_FLAG) values (seq_ratingType.nextVal, ?, ?, ?, ?)";
	private static final String INSERT_ONE = "insert into s_ratingType(name, RATING_LEVEL_BY_TAGS, RATING_MAXSIMUN_SCORE, DELETE_FLAG) values (?, ?, ?, ?)";
	private static final String UPDATE_ONE = "update s_ratingType set name = ?, RATING_LEVEL_BY_TAGS = ?, RATING_MAXSIMUN_SCORE = ?, DELETE_FLAG = ? where seq = ? ";
	private static final String DELETE_ONE = "update s_ratingType set DELETE_FLAG = 1 where seq = ? ";
}
